package com.wogu.jwa.classes;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import org.apache.log4j.Logger;

import com.mysql.jdbc.Statement;
import com.wogu.util.ArrayUtil;

public class Model {
	protected static Logger logger;
	protected final String tableName;
	protected final String SQL_INSERT;
	protected final String SQL_UPDATE;
	protected final String SQL_SELECT;
	protected final String SQL_DELETE;
	protected final String SQL_COUNT;
	private static Connection connection;
	private static final String tablePre;
	
	static {
		logger = Logger.getRootLogger();
		connection = AppConfig.getConnection();
		tablePre = AppConfig.getConfig().getProperty("tablepre");
	}
	
	public Model(String tableName) {
		this.tableName = tablePre + tableName;
		SQL_INSERT = "INSERT INTO " + this.tableName + " ($1) VALUES($2)";
		SQL_UPDATE = "UPDATE " + this.tableName + " SET $1 WHERE $2";
		SQL_SELECT = "SELECT $1 FROM " + this.tableName + " WHERE $2 ORDER BY $3 LIMIT $4";
		SQL_DELETE = "DELETE FROM " + this.tableName + " WHERE $1";
		SQL_COUNT = "SELECT COUNT(*) FROM " + this.tableName + " WHERE $1";
	}
	
	public int insert(Map<String, String> data, boolean rtnInsertId) throws SQLException {
		String[] keys = data.keySet().toArray(new String[0]);
		String[] vals = data.values().toArray(new String[0]);
		int len = vals.length;
		String sql = SQL_INSERT.replace("$1", ArrayUtil.join(keys, ","));
		sql = sql.replace("$2", ArrayUtil.join(ArrayUtil.repeat("?", len), ","));

		int code;
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			for(int i = 0; i < len; i++) {
				ps.setString(i + 1, vals[i]);
			}
			code = ps.executeUpdate();
			if(rtnInsertId) {
				ResultSet rs = ps.getGeneratedKeys();
				rs.next();
				code = rs.getInt(1);
			}
		} finally {
			ps.close();
		}

		return code;
	}
	
	public int insert(Map<String, String> data) throws SQLException {
		return insert(data, false);
	}
	
	public int update(HashMap<String, String> data, HashMap<String, String> where) throws SQLException {
		String[] dataKeys = data.keySet().toArray(new String[0]);
		String[] dataVals = data.values().toArray(new String[0]);
		String[] whereKeys = where.keySet().toArray(new String[0]);
		String[] whereVals = where.values().toArray(new String[0]);
		int dataLen = dataVals.length;
		int totalLen = dataLen + whereVals.length;
		
		String sql = SQL_UPDATE.replace("$1", toSql(dataKeys, ","));
		sql = sql.replace("$2", toSql(whereKeys, "AND"));
		
		int code = 0;
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
			int i = 0;
			for(; i < dataLen; i++) {
				ps.setString(i + 1, dataVals[i]);
			}
			
			for(; i < totalLen; i++) {
				ps.setString(i + 1, whereVals[i - dataLen]);
			}
			
			code = ps.executeUpdate();
		} finally {
			ps.close();
		}

		return code;
	}
	
	public int update(HashMap<String, String> data, String where, String[] whereData) throws SQLException {
		String[] dataKeys = data.keySet().toArray(new String[0]);
		String[] dataVals = data.values().toArray(new String[0]);
		int dataLen = dataVals.length;
		String sql = SQL_UPDATE.replace("$1", toSql(dataKeys, ","));
		sql = sql.replace("$2", where);
		int code = 0;
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
			int i = 0;
			for(; i < dataLen; i++) {
				ps.setString(i + 1, dataVals[i]);
			}
			
			if(whereData != null) {
				for(int m = 0; m < whereData.length; m++) {
					ps.setString(i + m + 1, whereData[m]);
				}
			}
			
			code = ps.executeUpdate();
		} finally {
			ps.close();
		}
		
		return code;
	}
	
	public int update(HashMap<String, String> data) throws SQLException {
		return update(data, "1", null);
	}
	
	public ResultSet select(String fields, String where, String[] whereData, String order,
							String limit, int resultSetType, int resultSetConcurrency) throws SQLException {
		String sql = SQL_SELECT.replace("$1", fields == null ? "*" : fields);
		
		if(where == null)
			sql = sql.replace("WHERE $2 ", "");
		else
			sql = sql.replace("$2", where);
			
		if(order == null)
			sql = sql.replace("ORDER BY $3 ", "");
		else
			sql = sql.replace("$3", order);
			
		if(limit == null)
			sql = sql.replace(" LIMIT $4", "");
		else
			sql = sql.replace("$4", limit);

		PreparedStatement ps = connection.prepareStatement(sql, resultSetType, resultSetConcurrency);
		if(whereData != null) {
			int len = whereData.length;
			for(int i = 0; i < len; i++) {
				ps.setString(i+1, whereData[i]);
			}
		}
		
		return ps.executeQuery();
	}
	
	public ResultSet select(String fields, String where, String[] whereData, String order, String limit) throws SQLException {
		return select(fields, where, whereData, order, limit, 0, 0);
	}
	
	public int delete(String where, String[] whereData) throws SQLException {
		String sql = SQL_DELETE.replace("$1", where);
		int code = 0;
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
			if(whereData != null) {
				int len = whereData.length;
				for(int i = 0; i < len; i++) {
					ps.setString(i+1, whereData[i]);
				}
			}
			code = ps.executeUpdate();
			return code;
		} finally {
			ps.close();
		}
	}
	
	public int count(String where, String[] whereData) throws SQLException {
		String countSQL = SQL_COUNT;
		if(where == null)
			countSQL = countSQL.replace(" WHERE $1", "");
		else
			countSQL = countSQL.replace("$1", where);
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(countSQL);
			if(whereData != null) {
				int len = whereData.length;
				for(int i = 0; i < len; i++) {
					ps.setString(i+1, whereData[i]);
				}
			}
			ResultSet rs = ps.executeQuery();
			if(!rs.next()) {
				return 0;
			}
			
			return rs.getInt(1);
		} finally {
			ps.close();
		}
	}
	
	public Map<String, Object> list(int page, int perPage, String fields, String where, String[] whereData, String order, String urlRule) throws SQLException {
		int total = count(where, whereData);
		if(total == 0) return null;
		HashMap<String, Object> data = new HashMap<>();
		
		String sql = SQL_SELECT.replace("$1", fields == null ? "*" : fields);
		
		if(where == null)
			sql = sql.replace("WHERE $2 ", "");
		else
			sql = sql.replace("$2", where);
			
		if(order == null)
			sql = sql.replace("ORDER BY $3 ", "");
		else
			sql = sql.replace("$3", order);

		String limit = String.valueOf((page - 1) * perPage) + ',' + String.valueOf(perPage);
		sql = sql.replace("$4", limit);

		PreparedStatement ps = null;
		ps = connection.prepareStatement(sql);
		if(whereData != null) {
			int len = whereData.length;
			for(int i = 0; i < len; i++) {
				ps.setString(i+1, whereData[i]);
			}
		}

		ResultSet rs = ps.executeQuery();
		data.put("items", rs);
		Page pageObj = new Page(page, perPage, total, urlRule);
		String pages = pageObj.createPrePage().createCore().createNextPage().output();
		data.put("pages", pages);
		return data;
	}
	
	private String toSql(String[] arr, String separator) {
		return ArrayUtil.join(arr, "=? " + separator + " ") + "=?";
	}
}